import requests
import datetime
import pandas as pd
import pymysql
from sklearn.preprocessing import MinMaxScaler
from joblib import dump

class WeatherUtils(object):
    """天气类

    Args:
        object (_type_):_description_
    """
    def __inft__(self):
        self.date_list = [
            '2024-07-81',
            '2024-08-01',
            '2024-09-01',
            
            '2024-10-81',
            '2024-11-81',
            '2024-12-81',
        ]    
        self.url = 'http://v1.yiketianqi.com/api'
    def get__data(self):
        """取天气数据
        """
        data_list= []
        for d in self.date_list:
            conf  = {
            'appid':'88249599', #使用自己注册的id
            'appsecret':'BA7zIjr',
            'version':'history',
            'year': d[:4],
            'month': d[5:7],
            'city': '南昌'
            }
#发起请求获取数据
            res = requests.get(self.url + '?', params = conf)
            res_data = res.json()
            #print(res_data)
            #break
            for i in res_data['data']:
                data_list.append({
                    'date': datetime.datetime.strptime(i['ymd'], '%Y-%m-%d'),
                    'bhendu': i['bkendu'],
                    'yHendu': i['yWendu'],
                    'tianqi': i['tianqi'],
                    'fengli': i['fengli'],
                })
            df = pd.DataFrame(data_list)
            df.to_csv('./timing/weather.csv')   


class MysqlUtils(object):
    def __init__(self) -> None:
        self.conn = pymysql.connect(
            host='127.0.0.1',
            user='root',
            password='12345',
            database='scenic',
            port=3306,
            charset='utf8mb4'
        )
        self.weather_date = pd.read_csv('./timing/weather.csv')

    def is_holiday(self, date):
        """是否节假日判断
        """

        if date in ['2024-09-03', '2024-10-01', '2024-10-02', '2024-10-03', '2024-10-04', '2024-10-05', '2024-10-06', '2024-10-07', '2025-01-01', '2025-01-02', '2025-01-03']:
            return 1
        return 0

    def get_data(self):
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = """
       SELECT DATE(g.create_time) as date, HOUR(g.create_time) as hour, count(*) as count FROM order_user_gate_rel g WHERE HOUR(g.create_time) BETWEEN 6 and 23 GROUP BY date, hour
        """
        cursor.execute(sql)
        ret = cursor.fetchall()
        df = pd.DataFrame(ret)

        #合并天气数据
        self.weather_date['date'] = pd.to_datetime(self.weather_date['date'])
        df['date'] = pd.to_datetime(df['date'])
        df_pivot = pd.merge(self.weather_date, df, on='date')
        print(df_pivot.head)
        df_pivot.set_index('date', inplace=True)

        df_pivot['dow']= df_pivot.index.dayofweek #星期几.(0-6)
        df_pivot['month']= df_pivot.index.month #月份
        df_pivot['is_holiday']= df_pivot.index.map(self.is_holiday)
        # print(df_pivot.head)
        
        #对星期几和月份进行独热编码
        df_pivot = pd.get_dummies(df_pivot, columns=['dow', 'month', 'tianqi', 'fengli'],dtype=int)
        #对温度进行类型转换
        df_pivot['bWendu']= df_pivot['bwendu'].str.replace('°','').astype(int)
        df_pivot['ywendu']= df_pivot['ywendu'].str.replace('°','').astype(int)
        #归一化入园数
        scaler = MinMaxScaler()
        features = df_pivot[['count']]
        df_pivot['count'] = scaler.fit_transform(features)
        # 归一化天气
        weather_features = df_pivot[['bWendu','yWendu']]
        dump(scaler, 'timing/scaler.joblib')
        df_pivot[['bwendu','ywendu']]= scaler.fit_transform(weather_features)
        dump(weather_features,"timing/weather_features.joblib")
        df_pivot.to_csv('timing/scenic_data.csv', index=False)

if __name__ == 'main':
    # wu = WeatherUtils()
    # wu.get_data()
    mu = MysqlUtils()
    mu.get_data()